package com.mach.platform.util;

import com.alibaba.druid.pool.DruidDataSource;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import lombok.extern.slf4j.Slf4j;

import javax.sql.DataSource;
import java.io.UnsupportedEncodingException;
import java.sql.*;
import java.util.*;

@Slf4j
public class SqlHelper {

	private static SqlHelper me = null;

	public static SqlHelper get(){
		if(me == null){
			me = new SqlHelper();
		}
		return me;
	}

	public static SqlHelper getHasSourceNotUse(){
		return null;
	}

	public static SqlHelper getBySource(DataSource dataSource){
		SqlHelper hlp = new SqlHelper();
		if(dataSource == null){
			return null;
		}
		hlp.setDataSource(dataSource);
		return hlp;
	}

	protected DataSource dataSource;

	protected SqlHelper(){
	}

	protected SqlHelper(DataSource dataSource){
		this.dataSource = dataSource;
	}

	public static DataSource getExtDataSource() {
		DruidDataSource dataSource = new DruidDataSource();
//		dataSource.setUrl("jdbc:mysql://121.201.47.137:3306/renault?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false");
//		dataSource.setUsername("root");
//		dataSource.setPassword("Gzsr66@mysql.cn");

		dataSource.setUrl("jdbc:mysql://localhost/ext?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false");
		dataSource.setUsername("shareData");
		dataSource.setPassword("abc123123");

		dataSource.setInitialSize(5);
		dataSource.setMaxActive(100);

		return dataSource;
	}

	public static DataSource getGoDataSource() {
		DruidDataSource dataSource = new DruidDataSource();

		//140.143.59.111
		dataSource.setUrl("jdbc:mysql://121.201.47.137:3306/gogo?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false");
		dataSource.setUsername("root");
		dataSource.setPassword("windowsgomp1");

		dataSource.setInitialSize(5);
		dataSource.setMaxActive(100);

		return dataSource;
	}

	public static SqlHelper getGoHelper(){
		return new SqlHelper(getGoDataSource());
	}


	public DataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	public int update(String sql) throws SQLException{

		int update_cnt = 0;
		Connection conn = null;
		try {
//			log.info( "sql=" + sql);
			conn = this.dataSource.getConnection();
			PreparedStatement stat = conn.prepareStatement(sql);
			update_cnt = stat.executeUpdate();
		} catch (SQLException e) {
			throw e;
		}finally{
			clConn(conn);
		}
		return update_cnt;
	}

	class UpdateRuner implements Runnable{

		SqlHelper helper = null;
		String sql = "";
		public UpdateRuner(SqlHelper helper,String sql){
			this.helper = helper;
			this.sql = sql;
		}

		@Override
		public void run() {
			try {
				this.helper.update(this.sql);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public void updateAsync(String sql){
		U.cachedThreadPool.execute(new UpdateRuner(this,sql));
	}

	/**
	 * 查询 语句返回 Map的集合结果
	 *
	 * @param sql
	 * @param args
	 * @return
	 */
	public List<Map<String, Object>> qarMapList (String sql, Object... args) {

		List<Map<String, Object>> results = Lists.newArrayList();

		Connection connection = null;
		ResultSet resultSet = null;
		PreparedStatement preparedStatement = null;

		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(sql);

			if (args != null) {
				for (int i = 0; i < args.length; i++) {
					preparedStatement.setObject(i + 1, args[i]);
				}
			}

			resultSet = preparedStatement.executeQuery();

			results = toMapList(resultSet);

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			clConn(connection);
		}

		return results;
	}

	public List<Map<String, Object>> toMapList (ResultSet resultSet) throws SQLException {
		/**
		 * 5. 准备一个 List<Map<String, Object>>，键: 存放列的别名, 值: 存放列的值. 其中一个 Map 对象对应着一条记录
		 */
		List<Map<String, Object>> values = Lists.newArrayList();

		List<String> columnLabels = getColumnLabels(resultSet);
		Map<String, Object> map;

		// 7. 处理 ResultSet, 使用 while 循环
		while (resultSet.next()) {
			map = Maps.newHashMap();

			for (String columnLabel : columnLabels) {
				Object value = resultSet.getObject(columnLabel);
				map.put(columnLabel, value);
			}

			// 11. 把一条记录的一个 Map 对象放入 5 准备的 List 中
			values.add(map);
		}
		return values;
	}

	private List<String> getColumnLabels (ResultSet rs) throws SQLException {
		List<String> labels = Lists.newArrayList();

		ResultSetMetaData rsmd = rs.getMetaData();
		for (int i = 0; i < rsmd.getColumnCount(); i++) {
			labels.add(rsmd.getColumnLabel(i + 1));
		}

		return labels;
	}


	public Map<Object,Map<String,Object>> query(String sql) throws SQLException{
		Map<Object,Map<String,Object>> resultMap = new HashMap<Object,Map<String,Object>>();
		Connection conn = null;
		try {

//			log.info("sql=" + sql);
			conn = this.dataSource.getConnection();
			PreparedStatement stat = conn.prepareStatement(sql);
			long st = System.currentTimeMillis();
			ResultSet resultSet = stat.executeQuery();
			ResultSetMetaData meta = resultSet.getMetaData();

//			for(int i = 1; i <= meta.getColumnCount(); ++i){
//				String label = meta.getColumnLabel(i);
//				String name = meta.getColumnName(i);
//				log.info("i=" + i + " label=" + label + " name=" + name);
//			}
			Integer cnt = 0;
			while( resultSet.next() ){
				Map<String,Object> item = new HashMap<String,Object>();

				for(int i = 1; i <= meta.getColumnCount(); ++i){
					String label = meta.getColumnLabel(i);
					Object value = resultSet.getObject(i);
					item.put(label, value);
				}

				resultMap.put(cnt, item);

				++cnt;
			}

		} catch (SQLException e) {
			throw e;
		}finally{
			clConn(conn);
		}

		resultMap = transIndexValue2String(resultMap);

		return resultMap;
	}

	public List<Map<String,Object>> queryList(String sql) throws SQLException{
		List<Map<String,Object>> resultList = new LinkedList<>();
		Connection conn = null;
		try {
			conn = this.dataSource.getConnection();
			PreparedStatement stat = conn.prepareStatement(sql);
			ResultSet resultSet = stat.executeQuery();
			ResultSetMetaData meta = resultSet.getMetaData();

			Integer cnt = 0;
			while( resultSet.next() ){
				Map<String,Object> item = new HashMap<String,Object>();

				for(int i = 1; i <= meta.getColumnCount(); ++i){
					String label = meta.getColumnLabel(i);
					Object value = resultSet.getObject(i);
					item.put(label, value);
				}

				resultList.add(item);

				++cnt;
			}

		} catch (SQLException e) {
			throw e;
		}finally{
			clConn(conn);
		}

		return resultList;
	}

	/*
	 * 查出单项值
	 */
	public Map<String,Object> queryOne(String sql) throws SQLException{
		Map<String,Object> resultMap = new HashMap<String,Object>();
		Connection conn = null;
		try {
//			log.info("sql=" + sql);
			conn = this.dataSource.getConnection();
			PreparedStatement stat = conn.prepareStatement(sql);
			ResultSet resultSet = stat.executeQuery();
			ResultSetMetaData meta = resultSet.getMetaData();

			if( resultSet.next() ){
				for(int i = 1; i <= meta.getColumnCount(); ++i){
					String label = meta.getColumnLabel(i);
					Object value = resultSet.getObject(i);
					resultMap.put(label, value);
				}
			}

		} catch (SQLException e) {
			throw e;
		}finally{
			clConn(conn);
		}

		return resultMap;
	}


	private String subQuerySQL(String querySQL) {
		if(U.isBlk(querySQL)){
			return null;
		}
		StringBuilder sb = new StringBuilder();
		sb.append("(");
		sb.append(querySQL);
		sb.append(")");
		return sb.toString();
	}

	private String makeAllTextSeachSQL(String keyWord, List<String> findFields){
		return makeAllTextSeachSQL(keyWord, findFields, true);
	}

	private String makeAllTextSeachSQL(String keyWord, List<String> findFields,boolean supportManyKey) {

		if(U.isBlk(keyWord)){
			return "";
		}

		List<String> keyWordList = new ArrayList<>();
		if(supportManyKey){
			String[] keyWords = keyWord.split(" ");
			for(String kw : keyWords){
				keyWordList.add(kw);
			}

		}else{
			keyWordList.add(keyWord);
		}

		String sql = "";
		StringBuilder sqlBd = new StringBuilder();
		sqlBd.append("(");

		for(String kword : keyWordList){
			sqlBd.append("(");
			for(int i = 0; i < findFields.size(); ++i){

				String field = findFields.get(i);
				sqlBd.append(field);
				sqlBd.append(" like BINARY ");
				sqlBd.append("'%");
				sqlBd.append(kword);
				sqlBd.append("%'");
				sqlBd.append(" or ");

			}
			sqlBd.append(" 1<>1 ");
			sqlBd.append(") \r\n and ");
		}
		sqlBd.append(" 1=1 \r\n ");
		sqlBd.append(") \r\n ");

		sql = sqlBd.toString();
		return sql;
	}



	synchronized static public Long getOperaId(){
		Long operaId = System.currentTimeMillis();

		return operaId;
	}

	public static void clConn(Connection conn){
		if(null != conn){
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public static void clStat(Statement stat){
		if(null != stat){
			try {
				stat.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}


	public static void clStat(PreparedStatement stat){
		if(null != stat){
			try {
				stat.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public String data2CSV( Map<Object,Map<String,Object>> datas, Map<String, String> tableMeta) throws UnsupportedEncodingException{
		StringBuilder dataCsv = new StringBuilder();

		Collection<Map<String, Object>> values = datas.values();

		Set<String> headFields = tableMeta.keySet();

		for(String head : headFields){
			String headName = tableMeta.get(head);
			dataCsv.append(",").append(headName);
		}
		dataCsv.append("\r\n");

		for(Map<String, Object> item : values){
			for(String head : headFields){
				Object value = item.get(head);
				if(value == null){
					value = "";
				}
				dataCsv.append(",").append(value.toString());
			}
			dataCsv.append("\r\n");
		}

		String fileStr = dataCsv.toString();
		return fileStr;
	}

	public static Map<Object,Map<String,Object>> transIndexValue2String(Map<Object,Map<String,Object>> data){
		if(data == null)return Collections.EMPTY_MAP;

		Map<Object,Map<String,Object>> finalResult = new HashMap<Object,Map<String,Object>>();


		Set<Map.Entry<Object, Map<String, Object>>> eSet = data.entrySet();

		for(Map.Entry<Object, Map<String, Object>> en : eSet){
			Object key = en.getKey();
			Map<String,Object> value = en.getValue();

			finalResult.put(key.toString(), value);
		}

		return finalResult;
	}

	public void initDefDataSource_NotUse(){
//		DruidDataSource dataSource = new DruidDataSource();
//		dataSource.setUrl("jdbc:mysql://121.201.20.142:3306/renault?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false");
//		dataSource.setUsername("root");
//		dataSource.setPassword("Gzsr66@mysql.cn");

//		dataSource.setUrl("jdbc:mysql://118.89.37.80:3306/iotdb?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false");
//		dataSource.setUsername("root");
//		dataSource.setPassword("ems4159");

//		dataSource.setInitialSize(1);
//		dataSource.setMaxActive(100);

//		this.dataSource = getNissanDataSource();
//		this.dataSource = getRenaultDataSource();

//		this.dataSource = MachineConfig

	}

	public static DataSource getRenaultDataSource() {
		DruidDataSource dataSource = new DruidDataSource();
//		dataSource.setUrl("jdbc:mysql://121.201.20.142:3306/renault?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false");
//		dataSource.setUsername("root");
//		dataSource.setPassword("Gzsr66@mysql.cn");

		dataSource.setUrl("jdbc:mysql://192.168.1.120:3306/renault?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false");
		dataSource.setUsername("root");
		dataSource.setPassword("667iot766");

		dataSource.setInitialSize(5);
		dataSource.setMaxActive(100);

		return dataSource;
	}
	public static DataSource getNissanDataSource() {
		DruidDataSource dataSource = new DruidDataSource();
		dataSource.setUrl("jdbc:mysql://121.201.47.137:3306/stationModel?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false");
		dataSource.setUsername("root");
		dataSource.setPassword("windowsgomp1");

		dataSource.setInitialSize(5);
		dataSource.setMaxActive(100);

		return dataSource;
	}

	public static DataSource getMSSQLDataSource() throws ClassNotFoundException {
		Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		DruidDataSource dataSource = new DruidDataSource();
		dataSource.setUrl("jdbc:sqlserver://192.168.1.100:1433;DatabaseName=PECSTAR_DATA_DFFS");
		dataSource.setUsername("CET_DATA");
		dataSource.setPassword("CET_DATA");

		dataSource.setInitialSize(1);
		dataSource.setMaxActive(10);

		return dataSource;
	}

	public static SqlHelper getMSSQLHelper() throws ClassNotFoundException {
		SqlHelper helper = new SqlHelper();
		helper.setDataSource(getMSSQLDataSource());
		return helper;
	}
//	public static void main(String[] args) throws SQLException {
//		SqlHelper helper = SqlHelper.getBySource(SqlHelper.getNissanDataSource());
////		String sql = " select id, name from iot_dev limit 10 ";
////		Map<Object, Object> map = helper.getMapFrTable("id", "name", sql);
////		String sql="select max(uptime) from iot_dev_cur_data_win";
////		Map<String,Object>  map=helper.queryOne(sql);
//		String sql="select dev_id,data_type,dvalue,unit,uptime from iot_dev_cur_data_win";
//		List<Map<String, Object>> list=helper.qarMapList(sql);
//		System.out.println(list);
////		test3();
//	}


	public static void test3() throws SQLException {
		SqlHelper helper = SqlHelper.getBySource(SqlHelper.getNissanDataSource());
		String sql2GetDevs = "select dev.id devId, dev.name name,type.prol prol,  dev.dev_type_id devTypeId,\n" +
				" coll.id collId, coll.name collName, coll.value_addr collAddr, \n" +
				" coll.data_len collLen, coll.data_type collDataType,\n" +
				" '' from iot_dev dev\n" +
				" LEFT JOIN iot_coll_items coll on dev.dev_type_id=coll.dev_type_id\n" +
				" LEFT JOIN iot_dev_type type on type.id=dev.dev_type_id " +
				" where dev.id in (19,20)" +
				"	and coll.is_get=1 ";
		String sql1="select dev_id,data_type,dvalue,unit,uptime from iot_dev_cur_data_win";
		Map<Object, Map<String, Object>> dbObj =
				helper.queryPairModel(sql1,
						new PairModel("dev", "devId","coll",true));

	//	Object typeObj = dbObj.get("19").get("coll");

		System.out.println(dbObj);
	}


	public Map<Object,Object> getMapFrTable(String keyField, String valueField, String sql) throws SQLException {
		Map<Object,Object> resultMap = new HashMap<Object,Object>();
		Connection conn = null;
		try {
			conn = this.dataSource.getConnection();
			PreparedStatement stat = conn.prepareStatement(sql);
			ResultSet resultSet = stat.executeQuery();

			ResultSetMetaData meta = resultSet.getMetaData();

			Integer cnt = 0;
			while( resultSet.next() ){
				Object keyValue = null;
				Object valueObj = null;
				for(int i = 1; i <= meta.getColumnCount(); ++i){
					String label = meta.getColumnLabel(i);
					Object value = resultSet.getObject(i);
					if(label.equals(keyField)){
						keyValue = value;
					}
					if(label.equals(valueField)){
						valueObj = value;
					}
				}

				resultMap.put(keyValue, valueObj);

				++cnt;
			}

		} catch (SQLException e) {
			throw e;
		}finally{
			clConn(conn);
		}

		return resultMap;
	}

	public Map<Object,Map<String,Object>> queryPairModel(String sql, PairModel pairModel)
			throws SQLException{
		Map<Object,Map<String,Object>> resultMap = new HashMap<Object,Map<String,Object>>();
		Connection conn = null;
		try {
//			log.info("sql=" + sql);
			conn = this.dataSource.getConnection();
			PreparedStatement stat = conn.prepareStatement(sql);
			long st = System.currentTimeMillis();
			ResultSet resultSet = stat.executeQuery();

			ResultSetMetaData meta = resultSet.getMetaData();

			Integer cnt = 0;
			Integer jionCnt = 0;
			while( resultSet.next() ){
				Map<String,Object> item = null;


				String keyName = null;
				Object keyValue = null;

				Map<Object,Object> jionObj = null;

				for(int i = 1; i <= meta.getColumnCount(); ++i){
					String label = meta.getColumnLabel(i);
					Object value = resultSet.getObject(i);

					if(label.equals(pairModel.getKey())){
						keyName = label;
						keyValue = value;

						item = resultMap.get(keyValue.toString());

						if(item == null){
							item = new HashMap<>();
						}
					}

					if(label.startsWith(pairModel.getJionClass())){ //子类
						String jionClass = pairModel.getJionClass();
						jionObj = (Map<Object,Object>) item.get(jionClass);

						if(null == jionObj){
							jionCnt = 0;
							jionObj = new HashMap<>();
							item.put(jionClass, jionObj);
						}

						if(pairModel.getJionClass() != null && pairModel.getIsMany()){ //一对多
							Integer jionIx = jionCnt;
							Map<String, Object> jionIxObj = (Map<String, Object>) jionObj.get(jionIx);
							if(jionIxObj == null){
								jionIxObj = new HashMap<>();
								jionObj.put(jionIx, jionIxObj);
							}
							jionIxObj.put(label,value);

						}else{ //一对一
							jionObj.put(label, value);
						}

					}else{
						item.put(label, value);
					}


				}
				jionCnt++;

				if(keyName != null){
					resultMap.put(keyValue.toString(), item);
				}else{
					resultMap.put(cnt.toString(), item);
				}


				++cnt;
			}

		} catch (SQLException e) {
			throw e;
		}finally{
			clConn(conn);
		}

		return resultMap;
	}


	public Model model(String modeName){
		Model model = new Model(this);
		return model;
	}

	public class Model{
		private final SqlHelper helper;
		private String tableName;

		private Model(SqlHelper helper){
			this.helper = helper;
		}

		public Model table(String tableName){
			this.tableName = tableName;
			return this;
		}

	}

}
